use actix_web::{Error, HttpRequest, HttpResponse, Responder};
use anyhow::Result;
use futures::future::{ready, Ready};
use serde::{Deserialize, Serialize};
use sqlx::sqlite::SqliteRow;
use sqlx::{FromRow, Row, SqlitePool};
// extern crate log;
use log::info;
use chrono::prelude::*;
use serde_json;
// this struct will use to receive user input
#[derive(Serialize, Deserialize)]
pub struct ItemRequest {
    pub name: String,
    pub ct: i64,
}
#[derive(Serialize, Deserialize)]
pub struct ContactRequest {
    pub yonghu: String,
    pub yiqixinghao: String,
    pub yiqibh:String,
    pub baoxiang:String,
    pub shenhe:String,
    pub yujifahuo_date:chrono::NaiveDate,
    pub hetongbh:String,
    pub addr:String,
    pub channels:String,
    pub tiaoshi_date:chrono::NaiveDate,
    pub method:String,
    pub work_month:chrono::NaiveDate,
}
// #[derive(Deserialize)]
// pub struct InfoUpdate {
//     pub id:i64,
//     pub name: String,
//     pub ct:i64,
// }
// this struct will be used to represent database record
#[derive(Deserialize,Serialize, FromRow)]
pub struct Item {
    pub id: i64,
    pub name: String,
    pub ct: i64,
}

// implementation of Actix Responder for Item struct so we can return Item from action handler
impl Responder for Item {
    type Error = Error;
    type Future = Ready<Result<HttpResponse, Error>>;

    fn respond_to(self, _req: &HttpRequest) -> Self::Future {
        let body = serde_json::to_string(&self).unwrap();
        // create response and set content type
        ready(Ok(HttpResponse::Ok()
            .content_type("application/json")
            .body(body)))
    }
}

// Implementation for Item struct, functions for read/write/update and delete todo from database
impl Item {
    pub async fn find_all(pool: &SqlitePool) -> Result<Vec<Item>> {
        let mut todos = vec![];
        let recs = sqlx::query!(
            r#"
                SELECT id, name, ct
                    FROM parts_item
                ORDER BY id
            "#
        )
        .fetch_all(pool)
        .await?;

        for rec in recs {
            todos.push(Item {
                id: rec.id,
                name: rec.name,
                ct: rec.ct,
            });
        }

        Ok(todos)
    }

    pub async fn find_by_id(id: i64, pool: &SqlitePool) -> Result<Item> {
        let rec = sqlx::query!(
            r#"
                    SELECT * FROM parts_item WHERE id = $1
                "#,
            id
        )
        .fetch_one(&*pool)
        .await?;

        Ok(Item {
            id: rec.id,
            name: rec.name,
            ct: rec.ct,
        })
    }

    pub async fn create(todo: ItemRequest, pool: &SqlitePool) -> Result<Item> {
        // let mut tx = pool.begin().await.unwrap();
        let sql="INSERT INTO parts_item ( name ,ct)VALUES ( $1,$2)  RETURNING id, name, ct";
        let todo = sqlx::query(sql)
            .bind(&todo.name)
            .bind(todo.ct)
            .map(|row: SqliteRow| {
                Item {
                    id: row.get(0),
                    name: row.get(1),
                    ct: row.get(2)
                }
            })
            .fetch_one(&*pool)
            .await?;

        // tx.commit().await.unwrap();
        Ok(todo)
    }

    pub async fn update(todo: Item, pool: &SqlitePool) -> Result<Item> {
        // let mut tx = pool.begin().await.unwrap();
        let todo_r = sqlx::query("UPDATE parts_item SET name = $1, ct = $2 WHERE id = $3 RETURNING id, name, ct")
            .bind(&todo.name)
            .bind(todo.ct)
            .bind(todo.id)
            .map(|row: SqliteRow| {
                Item {
                    id: row.get(0),
                    name: row.get(1),
                    ct: row.get(2)
                }
            })
            .fetch_one(&*pool)
            .await?;

        // tx.commit().await.unwrap();
        Ok(todo_r)
    }

    pub async fn delete(id: i64, pool: &SqlitePool) -> Result<u64> {
        // let mut tx = pool.begin().await?;
        let deleted = sqlx::query("DELETE FROM parts_item WHERE id = $1")
            .bind(id)
            .execute(&*pool)
            .await?;

        // tx.commit().await?;
        Ok(11)
    }
    pub async fn sql(cmd:String, pool: &SqlitePool) -> Result<u64> {
        // let mut tx = pool.begin().await?;
        let deleted = sqlx::query(&cmd)
            .execute(&*pool)
            .await?;
        // tx.commit().await?;
        Ok(12)
    }
}
#[derive(Deserialize,Serialize, FromRow)]
pub struct Contact {
    pub id: i64,
    pub yonghu: String,
    pub yiqixinghao: String,
    pub yiqibh:String,
    pub baoxiang:String,
    pub shenhe:String,
    pub yujifahuo_date:chrono::NaiveDate,
    pub hetongbh:String,
    pub addr:String,
    pub channels:String,
    pub tiaoshi_date:chrono::NaiveDate,
    pub method:String,
    pub work_month:chrono::NaiveDate,
}
// ([id] integer PRIMARY KEY NOT NULL, [yonghu] varchar (30) NOT NULL, [yiqixinghao] varchar (30) NOT NULL, [yiqibh] varchar (30) NOT NULL UNIQUE, [baoxiang] varchar (30) NOT NULL, [shenhe] varchar (30) NOT NULL, [yujifahuo_date] DATE NOT NULL, [hetongbh] varchar (30) NOT NULL UNIQUE, [addr] VARCHAR (30), [channels] VARCHAR (30), [tiaoshi_date] DATE, [method] VARCHAR (200), [dianqi] VARCHAR (20), [jixie] VARCHAR (30), [hongwai] VARCHAR (20), [redao] VARCHAR (20), [detail] TEXT, [work_month] DATE)

// implementation of Actix Responder for Item struct so we can return Item from action handler
impl Responder for Contact {
    type Error = Error;
    type Future = Ready<Result<HttpResponse, Error>>;

    fn respond_to(self, _req: &HttpRequest) -> Self::Future {
        let body = serde_json::to_string(&self).unwrap();
        // create response and set content type
        ready(Ok(HttpResponse::Ok()
            .content_type("application/json")
            .body(body)))
    }
}

// Implementation for Item struct, functions for read/write/update and delete todo from database
impl Contact {
    pub async fn get(pool: &SqlitePool,offset:i64,limit:i64) -> Result<Vec<Contact>> {
        let mut todos = vec![];
        info!("before query");
        //,yiqixinghao,yiqibh,baoxiang,shenhe,yujifahuo_date,hetongbh,addr,channels,tiaoshi_date,method 
        let recs = sqlx::query(
            r#"
                SELECT id, yonghu,yiqixinghao,yiqibh,baoxiang,shenhe,yujifahuo_date,hetongbh,addr,channels,tiaoshi_date,method,work_month 
                    FROM parts_contact
                ORDER BY yujifahuo_date desc limit $1 offset $2
            "#
        ).bind(limit).bind(offset)
        .fetch_all(pool)
        .await?;
        info!("query ok");
        for rec in recs {
            todos.push(Contact {
                        id: rec.get("id"),
                        yonghu: rec.get("yonghu"),
                        yiqixinghao: rec.get("yiqixinghao"),
                        yiqibh:rec.get("yiqibh"),
                        baoxiang:rec.get("baoxiang"),
                        shenhe:rec.get("shenhe"),
                        yujifahuo_date:rec.get("yujifahuo_date"),
                        hetongbh:rec.get("hetongbh"),
                        addr:rec.get("addr"),
                        channels:rec.get("channels"),
                        tiaoshi_date:rec.get("tiaoshi_date"),
                        method:rec.get("method"),
                        work_month: {
                            let r=rec.try_get("work_month");
                            if let Ok(v)=r{
                                v
                            }
                            else {
                                NaiveDate::from_ymd(1, 1, 1)
                            }
                        }});
        }
        Ok(todos)
    }

    // pub async fn find_by_id(id: i64, pool: &SqlitePool) -> Result<Item> {
    //     let rec = sqlx::query!(
    //         r#"
    //                 SELECT * FROM parts_item WHERE id = $1
    //             "#,
    //         id
    //     )
    //     .fetch_one(&*pool)
    //     .await?;

    //     Ok(Item {
    //         id: rec.id,
    //         name: rec.name,
    //         ct: rec.ct,
    //     })
    // }
    pub async fn update(todo: serde_json::Value, pool: &SqlitePool) -> Result<Contact,sqlx::Error> {
        // let mut tx = pool.begin().await.unwrap();
        let sql="update parts_contact set yonghu=$1,yiqixinghao=$2,yiqibh=$3,baoxiang=$4,shenhe=$5,yujifahuo_date=$6,hetongbh=$7,addr=$8,channels=$9,tiaoshi_date=$10,method=$11,work_month=$12 where id=$13  RETURNING *";
        info!("before query");
        info!("{}",todo["yonghu"].as_str().unwrap());
        let contact = sqlx::query(sql)
            .bind(todo["yonghu"].as_str().unwrap())
            .bind(todo["yiqixinghao"].as_str().unwrap())
            .bind(todo["yiqibh"].as_str().unwrap())
            .bind(todo["baoxiang"].as_str().unwrap())
            .bind(todo["shenhe"].as_str().unwrap())
            .bind(todo["yujifahuo_date"].as_str().unwrap())
            .bind(todo["hetongbh"].as_str().unwrap())
            .bind(todo["addr"].as_str().unwrap())
            .bind(todo["channels"].as_str().unwrap())
            .bind(todo["tiaoshi_date"].as_str().unwrap())
            .bind(todo["method"].as_str().unwrap())
            .bind(todo["work_month"].as_str().unwrap())
            .bind(todo["id"].as_i64().unwrap())
            .map(|rec: SqliteRow| {
               Contact{ 
                id: rec.get("id"),
                yonghu: rec.get("yonghu"),
                yiqixinghao: rec.get("yiqixinghao"),
                yiqibh:rec.get("yiqibh"),
                baoxiang:rec.get("baoxiang"),
                shenhe:rec.get("shenhe"),
                yujifahuo_date:{
                    let r=rec.try_get("yujifahuo_date");
                    if let Ok(v)=r{
                        v
                    }
                    else {
                        NaiveDate::from_ymd(1, 1, 1)
                    }
                },
                hetongbh:rec.get("hetongbh"),
                addr:rec.get("addr"),
                channels:rec.get("channels"),
                tiaoshi_date:{
                    let r=rec.try_get("tiaoshi_date");
                    if let Ok(v)=r{
                        v
                    }
                    else {
                        NaiveDate::from_ymd(1, 1, 1)
                    }
                },
                method:rec.get("method"),
                work_month: {
                    let r=rec.try_get("work_month");
                    if let Ok(v)=r{
                        v
                    }
                    else {
                        NaiveDate::from_ymd(1, 1, 1)
                    }
                }}})
            .fetch_one(&*pool)
            .await?;

        // tx.commit().await.unwrap();
        Ok(contact)
    }
    pub async fn delete(id:i64, pool: &SqlitePool) -> Result<i64,sqlx::Error> {
        // let mut tx = pool.begin().await.unwrap();
        let sql="delete from  parts_contact  where id=$1";
        info!("before delete");
        info!("{}",id);
        let contact = sqlx::query(sql)
            .bind(id)
            .execute(&*pool)
            .await?;

        // tx.commit().await.unwrap();
        Ok(id)
    }

    pub async fn create(todo: serde_json::Value, pool: &SqlitePool) -> Result<Contact,sqlx::Error> {
        // let mut tx = pool.begin().await.unwrap();
        let sql="INSERT INTO parts_contact (yonghu,yiqixinghao,yiqibh,baoxiang,shenhe,yujifahuo_date,hetongbh,addr,channels,tiaoshi_date,method,work_month)VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)  RETURNING *";
        info!("before query");
        info!("{}",todo["yonghu"].as_str().unwrap());
        let contact = sqlx::query(sql)
            .bind(todo["yonghu"].as_str().unwrap())
            .bind(todo["yiqixinghao"].as_str().unwrap())
            .bind(todo["yiqibh"].as_str().unwrap())
            .bind(todo["baoxiang"].as_str().unwrap())
            .bind(todo["shenhe"].as_str().unwrap())
            .bind(todo["yujifahuo_date"].as_str().unwrap())
            .bind(todo["hetongbh"].as_str().unwrap())
            .bind(todo["addr"].as_str().unwrap())
            .bind(todo["channels"].as_str().unwrap())
            .bind(todo["tiaoshi_date"].as_str().unwrap())
            .bind(todo["method"].as_str().unwrap())
            .bind(todo["work_month"].as_str().unwrap())
            .map(|rec: SqliteRow| {
               Contact{ 
                id: rec.get("id"),
                yonghu: rec.get("yonghu"),
                yiqixinghao: rec.get("yiqixinghao"),
                yiqibh:rec.get("yiqibh"),
                baoxiang:rec.get("baoxiang"),
                shenhe:rec.get("shenhe"),
                yujifahuo_date:{
                    let r=rec.try_get("yujifahuo_date");
                    if let Ok(v)=r{
                        v
                    }
                    else {
                        NaiveDate::from_ymd(1, 1, 1)
                    }
                },
                hetongbh:rec.get("hetongbh"),
                addr:rec.get("addr"),
                channels:rec.get("channels"),
                tiaoshi_date:{
                    let r=rec.try_get("tiaoshi_date");
                    if let Ok(v)=r{
                        v
                    }
                    else {
                        NaiveDate::from_ymd(1, 1, 1)
                    }
                },
                method:rec.get("method"),
                work_month: {
                    let r=rec.try_get("work_month");
                    if let Ok(v)=r{
                        v
                    }
                    else {
                        NaiveDate::from_ymd(1, 1, 1)
                    }
                }}})
            .fetch_one(&*pool)
            .await?;

        // tx.commit().await.unwrap();
        Ok(contact)
    }

    // pub async fn delete(id: i64, pool: &SqlitePool) -> Result<u64> {
    //     let mut tx = pool.begin().await?;
    //     let deleted = sqlx::query("DELETE FROM parts_item WHERE id = $1")
    //         .bind(id)
    //         .execute(&mut tx)
    //         .await?;

    //     tx.commit().await?;
    //     Ok(11)
    // }
}
